-- @owner: @qumin20145
-- @date: 2022-06-14
-- @testpoint: group by后跟列的别名/常量/序列/case when表达式

--step1:建表并插入数据;expect:成功
drop table if exists t_ustore_query_0052 cascade ;
create table t_ustore_query_0052(id int constraint pk_ustore_query_0052  primary key,c_int int,c_vchar varchar(55) not null,c_vchar2 varchar(55) not null,c_blob blob not null,c_date date)
with (storage_type=ustore) partition by range(id)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300)
);

insert into t_ustore_query_0052 values(1,100,'abc123',lpad('123abc',50,'abc'),lpad('11100521',50,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
create or replace procedure proc_ustore_query_0052(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
  sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_vchar||'||i||',c_vchar2||'||i||',c_blob'||',c_date from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/
call proc_ustore_query_0052('t_ustore_query_0052',1,50);

--step2:创建序列;expect:成功
drop sequence if exists seq_ustore_query_0052;
create sequence seq_ustore_query_0052 start with 1 maxvalue 1000 increment by 1 cycle;

--step3:group by后跟列的别名;expect:成功
select id new_id,c_int from t_ustore_query_0052 group by new_id,c_int order by id ,c_int;

--step4:group by后跟常量;expect:成功
select id,c_int from t_ustore_query_0052 group by 1,id,c_int order by id ,c_int;

--step5:group by后跟序列;expect:成功
select id, c_int from t_ustore_query_0052 group by id,c_int,seq_ustore_query_0052.nextval order by id ,c_int;

--step6:group by后跟case when 表达式;expect:成功
select id,c_int from t_ustore_query_0052 group by (case when id <10 then id+1 else id+2 end),id,c_int order by id ,c_int;

--step7:清理环境; expect:成功
drop table t_ustore_query_0052 cascade;
drop procedure proc_ustore_query_0052;
drop sequence seq_ustore_query_0052;